package com.sailfish.springbootdemo.dao.db7;

import com.sailfish.springbootdemo.common.DB7TableName;
import com.sailfish.springbootdemo.pojo.db7.TbFlowProcessMain;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface TbFlowProcessMainDao {

    @Select("<script>" +
            "select * from " + DB7TableName.Tb_FLOW_PROCESS_MAIN + " " +
            "where 1=1 " +
            "   <if test='create_user != null or update_user != null'>and (" +
            "       <if test='create_user != null'>create_user = #{create_user}</if>" +
            "       <if test='update_user != null'>OR update_user = #{update_user}</if>" +
            "   )</if>" +
            "   <if test='create_user != null and update_user == null'>and create_user = #{create_user}</if>" +
            "   <if test='create_user == null and update_user != null'>and update_user = #{update_user}</if>" +
            "   <if test='status != null'>and status = #{status}</if>" +
            "   <if test='flow_no != null'>and flow_no = #{flow_no}</if>" +
            "   <if test='flow_type != null'>and flow_type = #{flow_type}</if>" +
            "   <if test='flow_type != null'>and flow_type = #{flow_type}</if>" +
            " order by flow_no desc,seq desc"+
            "</script>"
    )
    List<TbFlowProcessMain> getFlowProcessMain(TbFlowProcessMain tbFlowProcessMain);

    @Select("<script>" +
            "select * from " + DB7TableName.Tb_FLOW_PROCESS_MAIN + " " +
            "where 1=1 " +
            " and flow_type in ('design','device','vpl')"+
            "   <if test='entities.create_user != null or entities.update_user != null'>and (" +
            "       <if test='entities.create_user != null'>create_user = #{entities.create_user}</if>" +
            "       <if test='entities.update_user != null'>OR update_user = #{entities.update_user}</if>" +
            "   )</if>" +
            "   <if test='entities.create_user != null and entities.update_user == null'>and create_user = #{entities.create_user}</if>" +
            "   <if test='entities.create_user == null and entities.update_user != null'>and update_user = #{entities.update_user}</if>" +
            "   <if test='entities.status != null'>and status = #{entities.status}</if>" +
            "   <if test='entities.flow_no != null'>and flow_no = #{entities.flow_no}</if>" +
            "   <if test='entities.flow_type != null'>and flow_type = #{entities.flow_type}</if>" +
            "   <if test='entities.now_approver != null'>and now_approver = #{entities.now_approver}</if>" +
            " order by flow_no desc,seq desc"+
            "<if test='begin != null and pageSize != null and pageSize != \"\" '>" +
            " limit #{begin}, #{pageSize}" +
            "</if>" +
            "</script>"
    )
    List<TbFlowProcessMain> getFlowProcessMain2(@Param("entities") TbFlowProcessMain tbFlowProcessMain,
                                                @Param("begin") Integer begin,
                                                @Param("pageSize") Integer pageSize);

    @Select("<script>" +
            "select * from " + DB7TableName.Tb_FLOW_PROCESS_MAIN + " " +
            "where 1=1 " +
            " and status!=5 "+
            " and flow_type in ('design','device','vpl')"+
            "   <if test='entities.create_user != null or entities.update_user != null'>and (" +
            "       <if test='entities.create_user != null'>create_user = #{entities.create_user}</if>" +
            "       <if test='entities.update_user != null'>OR update_user = #{entities.update_user}</if>" +
            "   )</if>" +
            "   <if test='entities.create_user != null and entities.update_user == null'>and create_user = #{entities.create_user}</if>" +
            "   <if test='entities.create_user == null and entities.update_user != null'>and update_user = #{entities.update_user}</if>" +
            "   <if test='entities.status != null and entities.status !=2'>and status = #{entities.status}</if>" +
            "   <if test='entities.status != null and entities.status ==2'>and status in (1,2)</if>" +
            "   <if test='entities.flow_no != null'>and flow_no = #{entities.flow_no}</if>" +
            "   <if test='entities.flow_type != null'>and flow_type = #{entities.flow_type}</if>" +
            "   <if test='entities.now_approver != null'>and now_approver = #{entities.now_approver}</if>" +
            " order by flow_no desc,seq desc"+
            "<if test='begin != null and pageSize != null and pageSize != \"\" '>" +
            " limit #{begin}, #{pageSize}" +
            "</if>" +
            "</script>"
    )
    List<TbFlowProcessMain> getFlowProcessMain3(@Param("entities")TbFlowProcessMain tbFlowProcessMain,@Param("begin") Integer begin, @Param("pageSize") Integer pageSize);


    @Select("<script>" +
            "select * from " + DB7TableName.Tb_FLOW_PROCESS_MAIN + " " +
            "where 1=1 " +
            " and flow_type in ('design','device','vpl')"+
            "   <if test='entities.create_user != null or entities.update_user != null'>and (" +
            "       <if test='entities.create_user != null'>create_user = #{entities.create_user}</if>" +
            "       <if test='entities.update_user != null'>OR update_user = #{entities.update_user}</if>" +
            "   )</if>" +
            "   <if test='entities.create_user != null and entities.update_user == null'>and create_user = #{entities.create_user}</if>" +
            "   <if test='entities.create_user == null and entities.update_user != null'>and update_user = #{entities.update_user}</if>" +
            "   <if test='entities.status != null'>and status = #{entities.status}</if>" +
            "   <if test='entities.flow_no != null'>and flow_no = #{entities.flow_no}</if>" +
            "   <if test='entities.flow_type != null'>and flow_type = #{entities.flow_type}</if>" +
            "   <if test='entities.now_approver != null'>and now_approver = #{entities.now_approver}</if>" +
            "   <if test='flowList != null and !flowList.isEmpty()'> " +
            "   and flow_no IN " +
            "   <foreach item='item' collection='flowList' open='(' separator=',' close=')'>" +
            "   #{item} " +
            "   </foreach>" +
            "   </if>" +
            " order by flow_no desc,seq desc"+
            "<if test='begin != null and pageSize != null and pageSize != \"\" '>" +
            " limit #{begin}, #{pageSize}" +
            "</if>" +
            "</script>"
    )
    List<TbFlowProcessMain> getFlowProcessMain4(@Param("entities") TbFlowProcessMain tbFlowProcessMain,
                                                @Param("flowList") List<String> flowList,
                                                @Param("begin") Integer begin,
                                                @Param("pageSize") Integer pageSize);


    @Select("<script> " +
            "select " +
            "* " +
            "from " + DB7TableName.Tb_FLOW_PROCESS_MAIN +
            " where flow_no like concat(#{flowDay},'%') " +
            " order by seq desc LIMIT 1" +
            "</script>")
    TbFlowProcessMain getByFlowNo(@Param("flowDay") String flowDay);

    @Select("<script> " +
            "select " +
            "distinct knowledge_base_key " +
            "from " + DB7TableName.Tb_FLOW_PROCESS_MAIN +
            " where 1=1 " +
            "<if test='flowType != null and flowType != \"\"'>" +
            "  and  flow_type like concat('%', #{flowType},'%') " +
            "</if>" +
            "</script>")
    List<String> getByFlowType(@Param("flowType") String flowType);

    @Select("<script> " +
            "select " +
            " flow_no, create_time " +
            "from " + DB7TableName.Tb_FLOW_PROCESS_MAIN +
            " where knowledge_base_key = #{baseKey} " +
            " order by create_time desc " +
            "</script>")
    List<TbFlowProcessMain> getFlowProcessMainByBaseKey(@Param("baseKey") String baseKey);

    @Select("<script> " +
            "select " +
            "* " +
            "from " + DB7TableName.Tb_FLOW_PROCESS_MAIN +
            " where id = #{id} " +
            " order by seq desc LIMIT 1" +
            "</script>")
    List<TbFlowProcessMain> getFlowProcessMainById(@Param("id") Integer id);

    @Select("<script> " +
            "select " +
            "* " +
            "from " + DB7TableName.Tb_FLOW_PROCESS_MAIN +
            " where flow_no = #{flow_no} " +
            " order by seq desc LIMIT 1" +
            "</script>")
    List<TbFlowProcessMain> getFlowProcessMainByFlowNo(@Param("flow_no") String  flow_no);

    @Delete("<script>" +
            "delete from " + DB7TableName.Tb_FLOW_PROCESS_MAIN + " " +
            "where id = #{id}" +
            "</script>")
    Integer deleteFlowProcessMainById(@Param("id") Integer id);


    @Insert("<script>" +
            "insert into " + DB7TableName.Tb_FLOW_PROCESS_MAIN + " " +
            "(" +
            "flow_no," +
            "flow_type, " +
            "seq, " +
            "now_level, " +
            "now_approver, " +
            "status, " +
            "remark, " +
            "create_time, " +
            "create_user, " +
            "update_time, " +
            "update_user," +
            "knowledge_base_key "+
            ") " +
            "values " +
            "(" +
            "#{flow_no}, " +
            "#{flow_type}, " +
            "#{seq}, " +
            "#{now_level}, " +
            "#{now_approver}, " +
            "#{status}, " +
            "#{remark}, " +
            "#{create_time}, " +
            "#{create_user}, " +
            "#{update_time}, " +
            "#{update_user}, " +
            "#{knowledge_base_key} " +
            ")" +
            "</script>")
    Integer insertFlowProcessMain(TbFlowProcessMain tbFlowProcessMain);


    @Update("<script>" +
            "update " + DB7TableName.Tb_FLOW_PROCESS_MAIN +
            "<set>" +
            "<if test='now_level != null and now_level != 0'>" +
            "    now_level = #{now_level}," +
            "</if>" +
            "<if test='now_level == 0 '>" +
            "    now_level = Null," +
            "</if>" +
            "<if test='now_approver != null and now_approver != \"\"'>" +
            "    now_approver = #{now_approver}," +
            "</if>" +
            "<if test='now_approver == \"\"'>" +
            "    now_approver = Null," +
            "</if>" +
            "<if test='status != null '>" +
            "    status = #{status}," +
            "</if>" +
            "<if test='remark != null '>" +
            "    remark = #{remark}," +
            "</if>" +
            "<if test='update_time != null '>" +
            "    update_time = #{update_time}," +
            "</if>" +
            "<if test='update_user != null '>" +
            "    update_user = #{update_user}," +
            "</if>" +
            "<if test='knowledge_base_key != null '>" +
            "    knowledge_base_key = #{knowledge_base_key}," +
            "</if>" +
            "</set>" +
            "where flow_no=#{flow_no}" +
            "</script>")
    Integer updateFlowProcessMain(TbFlowProcessMain tbFlowProcessMain);


    @Update("<script>" +
            "update " + DB7TableName.Tb_FLOW_PROCESS_MAIN +
            "<set>" +
            "    now_approver = #{now_approver}," +
            "</set>" +
            "where status!=#{status} and now_approver=#{oldApprover} and flow_type=#{flow_type}" +
            "</script>")
    Integer updateFlowProcessMainFromDeatil(@Param("oldApprover") String oldApprover,
                                            @Param("now_approver") String now_approver,
                                            @Param("flow_type") String flow_type,
                                            @Param("status") Integer status
    );


    @Update("<script>" +
            " update " + DB7TableName.Tb_FLOW_PROCESS_MAIN +
            " <set>" +
            "     now_approver = #{now_approver}," +
            " </set>" +
            " where  flow_no=#{flow_no} and now_level=#{now_level}" +
            "</script>")
    Integer updateFlowProcessMainForAuthorize(@Param("now_approver") String now_approver,
                                            @Param("flow_no") String flow_no,
                                            @Param("now_level") Integer now_level
    );

    @Update("<script>" +
            " update " + DB7TableName.Tb_FLOW_PROCESS_MAIN +
            " <set>" +
            "     now_approver = #{now_approver}," +
            "     status = #{status},"+
            " </set>" +
            " where  flow_no=#{flow_no} and now_level=#{now_level}" +
            "</script>")
    Integer updateFlowProcessMainForAuthorize2(@Param("now_approver") String now_approver,
                                               @Param("flow_no") String flow_no,
                                               @Param("now_level") Integer now_level,
                                               @Param("status") Integer status
    );


    @Update("<script>" +
            "update " + DB7TableName.Tb_FLOW_PROCESS_MAIN +
            "<set>" +
            "    status = 5" +
            "</set>" +
            "where flow_no=#{flow_no}" +
            "</script>")
    Integer cancelFlowProcessMain(@Param("flow_no") String flow_no);


    @Update("<script>" +
            "update " + DB7TableName.Tb_FLOW_PROCESS_MAIN +
            "<set>" +
            "    is_send_vpl = 'Y' " +
            "</set>" +
            "where flow_no=#{flow_no}" +
            "</script>")
    Integer isSendToVpl(@Param("flow_no") String flow_no);


    @Select("<script> " +
            "select " +
            "* " +
            "from " + DB7TableName.Tb_FLOW_PROCESS_MAIN +
            " where IFNULL(is_send_vpl,'N') !='Y' and status=3 and flow_type='vpl' " +
            "</script>")
    List<TbFlowProcessMain> getNeedSendToVpl();

    @Select("<script>" +
            "SELECT * FROM " + DB7TableName.Tb_FLOW_PROCESS_MAIN +
            " WHERE knowledge_base_key IN " +
            "<foreach item='item' index='index' collection='baseKeys' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            " AND seq IN (" +
            "   SELECT MAX(seq) FROM " + DB7TableName.Tb_FLOW_PROCESS_MAIN +
            "   WHERE knowledge_base_key IN " +
            "   <foreach item='item' index='index' collection='baseKeys' open='(' separator=',' close=')'>" +
            "   #{item}" +
            "   </foreach>" +
            "   GROUP BY knowledge_base_key" +
            ")" +
            " ORDER BY seq DESC" +
            "</script>")
    List<TbFlowProcessMain> getFlowProcessMainsByBaseKeys(@Param("baseKeys") List<String> baseKeys);

    @Select("<script>" +
            "SELECT * FROM " + DB7TableName.Tb_FLOW_PROCESS_MAIN + " " +
            "<if test='flowList != null and !flowList.isEmpty()'> " +
            "WHERE flow_no IN " +
            "<foreach item='item' collection='flowList' open='(' separator=',' close=')'>" +
            "#{item} " +
            "</foreach>" +
            "</if>" +
            "</script>")
    List<TbFlowProcessMain> getFlowProcessMainByFlowList(@Param("flowList") List<String> flowList);

    @Select("<script>" +
            "SELECT * FROM " + DB7TableName.Tb_FLOW_PROCESS_MAIN + " " +
            " where status in (1,2) " +
            "</script>")
    List<TbFlowProcessMain> getFlowProcessMainByStatus();
}
